package platform_api.Repository;

import platform_api.Model.WeatherData;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface WeatherDataRepository extends CrudRepository<WeatherData, Integer> {

    /**
     * 获取一段时间的均值(指定字段)
     * @param city 城市id
     * @param column 字段
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return 均值
     */
    @Query(value = "select\n" +
            "CASE :col\n" +
            "    WHEN 'P0' THEN avg(P0)\n" +
            "    WHEN 'P' THEN avg(P)\n" +
            "    WHEN 'U' THEN avg(U)\n" +
            "    WHEN 'Ff' THEN avg(Ff)\n" +
            "    WHEN 'ff10' THEN avg(ff10)\n" +
            "    WHEN 'ff3' THEN avg(ff3)\n" +
            "    WHEN 'Tn' THEN avg(Tn)\n" +
            "    WHEN 'Tx' THEN avg(Tx)\n" +
            "    WHEN 'VV' THEN avg(VV)\n" +
            "    WHEN 'RRR' THEN avg(RRR)\n" +
            "    WHEN 'sss' THEN avg(sss)\n" +
            "    else avg(T)\n" +
            "END as result\n" +
            "FROM weather_data where " +
            "city_id = :city and  " +
            "observation_time between :startTime and :endTime", nativeQuery = true)
    Object getMeanOfSingleColumn(@Param("city") Integer city, @Param("col") String column, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    /**
     * 根据城市id和时间获取信息（所有字段）
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param city      城市id
     * @return  WeatherData
     */
    @Query(value = "select * " +
            "from weather_data " +
            "where city_id = :city " +
            "and observation_time " +
            "between :startTime and :endTime",
            nativeQuery = true)
    List<WeatherData> getByCityIdAndObservationTime(@Param("startTime") Date startTime, @Param("endTime") Date endTime, @Param("city") Integer city);

    /**
     * 获取一段时间最小值（指定字段）
     * @param city 城市id
     * @param column 字段
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return  最小值
     */
    @Query(value = "select\n" +
            "CASE :col\n" +
            "    WHEN 'P0' THEN min(P0)\n" +
            "    WHEN 'P' THEN min(P)\n" +
            "    WHEN 'U' THEN min(U)\n" +
            "    WHEN 'Ff' THEN min(Ff)\n" +
            "    WHEN 'ff10' THEN min(ff10)\n" +
            "    WHEN 'ff3' THEN min(ff3)\n" +
            "    WHEN 'Tn' THEN min(Tn)\n" +
            "    WHEN 'Tx' THEN min(Tx)\n" +
            "    WHEN 'VV' THEN min(VV)\n" +
            "    WHEN 'RRR' THEN min(RRR)\n" +
            "    WHEN 'sss' THEN min(sss)\n" +
            "    else min(T)\n" +
            "END as result\n" +
            "FROM weather_data where " +
            "city_id = :city and  " +
            "observation_time between :startTime and :endTime", nativeQuery = true)
    Object getMinValue(@Param("city") Integer city, @Param("col") String column, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Query(value = "select\n" +
            "CASE :col\n" +
            "    WHEN 'P0' THEN max(P0)\n" +
            "    WHEN 'P' THEN max(P)\n" +
            "    WHEN 'U' THEN max(U)\n" +
            "    WHEN 'Ff' THEN max(Ff)\n" +
            "    WHEN 'ff10' THEN max(ff10)\n" +
            "    WHEN 'ff3' THEN max(ff3)\n" +
            "    WHEN 'Tn' THEN max(Tn)\n" +
            "    WHEN 'Tx' THEN max(Tx)\n" +
            "    WHEN 'VV' THEN max(VV)\n" +
            "    WHEN 'RRR' THEN max(RRR)\n" +
            "    WHEN 'sss' THEN max(sss)\n" +
            "    else max(T)\n" +
            "END as result\n" +
            "FROM weather_data where " +
            "city_id = :city and  " +
            "observation_time between :startTime and :endTime", nativeQuery = true)
    Object getMaxValue(@Param("city") Integer city, @Param("col") String column, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

}